package top.hcode.hoj.tempjob;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.util.RandomUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import lombok.Data;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.nutz.lang.Lang;
import org.nutz.lang.util.NutMap;
import org.sagacity.sqltoy.config.annotation.Column;
import org.sagacity.sqltoy.config.annotation.Entity;
import org.sagacity.sqltoy.config.annotation.Id;
import org.sagacity.sqltoy.dao.LightDao;
import org.sagacity.sqltoy.model.MapKit;
import org.springframework.stereotype.Component;
import top.hcode.hoj.common.LazyDao;
import top.hcode.hoj.dao.org.OrgDao;
import top.hcode.hoj.dao.org.OrgUserDao;
import top.hcode.hoj.pojo.entity.org.Org;
import top.hcode.hoj.pojo.entity.org.OrgUser;
import top.hcode.hoj.service.admin.AdminOrgService;
import top.hcode.hoj.utils.Constants;
import top.hcode.hoj.utils.SqlUtil;

import java.io.InputStream;
import java.io.Serializable;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

import static top.hcode.hoj.utils.SqlUtil.sql;

/**
 * @author orangej
 * @since 2025/2/28
 */
@Slf4j
@Component
@RequiredArgsConstructor
public class ImportCampusStudentFromExcelService {

    private final OrgDao orgDao;
    private final OrgUserDao userDao;
    private final AdminOrgService orgService;
    private final LightDao lightDao;
    private final LazyDao lazyDao;
    private final AdminOrgService adminOrgService;

    private static final String tplSheetKeyword = "模板";
    private static final List<String> ignoreSchoolNameList = CollUtil.newArrayList("123武汉市光谷豹子溪小学");

    public void importCampusStudentFromExcel(InputStream input) {
        ExcelReader reader = ExcelUtil.getReader(input);
        List<Sheet> sheetList = reader.getSheets();

        // 忽略模板sheet
        sheetList = sheetList.stream()
                .filter(sheet -> !sheet.getSheetName().contains(tplSheetKeyword))
                .filter(sheet -> !ignoreSchoolNameList.contains(sheet.getSheetName()))
                .collect(Collectors.toList());

        // 从excel中读取数据，key：学校名，value：学生信息列表
        Map<String, List<StudentInfo>> schoolStudentsMap = readStudentInfo(sheetList);

        // 检查班级名称和学生名称是否有效
        checkClassAndStudentName(schoolStudentsMap);

        // 清空数据
        lightDao.executeSql("truncate table import_student_0228");

        // 导入到 import_student_0228
        List<StudentRecord> recordList = new ArrayList<>();
        schoolStudentsMap.forEach((schoolName, studentInfoList) -> {
            for (StudentInfo studentInfo : studentInfoList) {
                StudentRecord record = new StudentRecord();
                record.setSchoolName(schoolName);
                record.setCampusName(studentInfo.campusName);
                record.setClassName(studentInfo.className);
                record.setStudentName(studentInfo.studentName);
                record.setGradeNum(studentInfo.gradeNum);
                record.setClassNum(studentInfo.classNum);
                if (StrUtil.isNotBlank(studentInfo.username)) {
                    record.setUsername(studentInfo.username);
                }
                recordList.add(record);
            }
        });
        lightDao.saveAll(recordList);

        // 生成账号信息
        List<String> schoolNameList = sheetList.stream().map(Sheet::getSheetName).collect(Collectors.toList());
        importStudents(schoolNameList);

        // 打印sql，用于将用户名复制回粘贴回在线表格
        schoolNameList = lazyDao.queryString(SqlUtil.sql("select distinct school_name from import_student_0228"));
        for (String schoolName : schoolNameList) {
            String sql = SqlUtil.fmt("select username, student_name from import_student_0228 where school_name = '%s' order by id;", schoolName);
            System.out.println(sql);
        }
    }


    int parseGradeNum(String className) {
        return Integer.parseInt(StrUtil.sub(className, 0, -2));
    }

    int parseClassNum(String className) {
        return Integer.parseInt(StrUtil.subSufByLength(className, 2));
    }

    private void checkClassAndStudentName(Map<String, List<StudentInfo>> schoolStudentsMap) {
        List<String> errors = new ArrayList<>();

        for (String schoolName : schoolStudentsMap.keySet()) {
            List<StudentInfo> studentInfoList = schoolStudentsMap.get(schoolName);

            for (StudentInfo studentInfo : studentInfoList) {
                String className = studentInfo.className;
                if (className == null || className.isEmpty()) {
                    errors.add("学校: " + schoolName + " 的第" + studentInfo.rowNum + "行班级名称为空");
                    continue;
                }
                if (!StrUtil.isNumeric(className) || className.length() < 3) {
                    errors.add("学校: " + schoolName + " 的第" + studentInfo.rowNum + "行班级名称格式错误");
                    continue;
                }

                int gradeNum = parseGradeNum(className);
                int classNum = parseClassNum(className);
                if (gradeNum == 0 || gradeNum > 12) {
                    errors.add("学校: " + schoolName + " 的第" + studentInfo.rowNum + "行班级名称年级错误");
                    continue;
                }
                studentInfo.gradeNum = gradeNum;
                studentInfo.classNum = classNum;

                if (studentInfo.studentName == null || studentInfo.studentName.isEmpty()) {
                    errors.add("学校: " + schoolName + " 的第" + studentInfo.rowNum + "行学生名称为空");
                    continue;
                }
            }
        }

        if (!errors.isEmpty()) {
            throw new RuntimeException(CollUtil.join(errors, "\n"));
        }
    }

    private Map<String, List<StudentInfo>> readStudentInfo(List<Sheet> sheetList) {
        Map<String, List<StudentInfo>> result = new HashMap<>();
        for (Sheet sheet : sheetList) {
            String schoolName = sheet.getSheetName();
            List<StudentInfo> studentInfoList = new ArrayList<>();
            for (int i = 2; i < sheet.getPhysicalNumberOfRows(); i++) {
                Row row = sheet.getRow(i);
                String campusName = getCellStrValue(row.getCell(1));
                if (StrUtil.isBlank(campusName)) {
                    continue;
                }

                String username = getCellStrValue(row.getCell(0));
                if (StrUtil.isNotBlank(username)) {
                    continue;
                }

                String className = getCellStrValue(row.getCell(2));
                String studentName = getCellStrValue(row.getCell(3));
                StudentInfo s = new StudentInfo(i, campusName, className, studentName);
                s.username = username;
                studentInfoList.add(s);
            }
            result.put(schoolName, studentInfoList);
        }
        return result;
    }

    private String getCellStrValue(Cell cell) {
        if (cell == null) {
            return null;
        }

        if (cell.getCellTypeEnum() != CellType.STRING) {
            cell.setCellType(CellType.STRING);
        }

        String value = cell.getStringCellValue();
        if ("null".equals(value)) {
            return null;
        }

        return value.trim();
    }

    public void importStudents(List<String> schoolNameList) {
        // 更新 school_id
        lightDao.executeSql(sql("update import_student_0228 t0 left join\n" +
                                "    (select id, org_name\n" +
                                "     from org\n" +
                                "     where org_name in (:schoolNameList) and org_type = 2) t1\n" +
                                "    on t0.school_name = t1.org_name\n" +
                                "set t0.school_id = t1.id\n" +
                                "where t1.id is not null"), MapKit.map("schoolNameList", schoolNameList));

        // 检查 school
        List<String> unexistSchoolList = lazyDao.queryString(sql("select distinct school_name from import_student_0228 where school_id is null"));
        if (CollUtil.isNotEmpty(unexistSchoolList)) {
            throw new IllegalArgumentException(String.format("学校不存在：\n%s", CollUtil.join(unexistSchoolList, "\n")));
        }

        // 更新 campus_id
        lazyDao.runSql(sql("update import_student_0228 t0 left join org t1\n" +
                           "on t0.school_id = t1.parent_id and t0.campus_name = t1.org_name\n" +
                           "set t0.campus_id = t1.id"));

        // 检查 campus
        List<String> unexistCampusList = lazyDao.queryString(sql("select distinct campus_name from import_student_0228 where campus_id is null"));
        if (CollUtil.isNotEmpty(unexistCampusList)) {
            throw new IllegalArgumentException(String.format("校区不存在：\n%s", CollUtil.join(unexistCampusList, "\n")));
        }

        int gradeRetry = 0;
        while (gradeRetry < 2) {
            // 更新 campus_tree_code
            lazyDao.runSql(sql("update import_student_0228 t0 left join org t1\n" +
                               "on t0.campus_id = t1.id\n" +
                               "set t0.campus_tree_code = t1.tree_code where t0.campus_tree_code is null"));

            // 更新 grade_id
            List<String> campusCode = lazyDao.queryString(sql("select distinct campus_tree_code from import_student_0228 where campus_tree_code is not null"));
            campusCode.parallelStream().forEach(code -> {
                lazyDao.runSql(sql("update import_student_0228 t0 left join " +
                                   "(select * from org where tree_code like ? and  org_type = 6) t1\n" +
                                   "on t0.campus_tree_code = ? and t1.grade_num = t0.grade_num\n" +
                                   "set t0.grade_id = t1.id\n" +
                                   "where t1.id is not null"), code + "%", code);
            });

            // 检查 grade
            List<NutMap> unexistGradeList = lazyDao.queryMap(sql("select distinct school_name, grade_num from import_student_0228 where grade_id is null"));
            if (unexistGradeList.size() == 0) {
                break;
            }
            if (gradeRetry > 0) {
                throw new IllegalArgumentException(String.format("年级不存在：\n%s", CollUtil.join(unexistGradeList
                                .stream().map(it -> String.format("学校：%s 年级：%s", it.getString("schoolName"), it.getString("gradeNum")))
                                .collect(Collectors.toList())
                        , "\n")));
            } else {
                for (NutMap map : unexistGradeList) {
                    String schoolName = map.getString("schoolName");
                    int gradeNum = map.getInt("gradeNum");

                    adminOrgService.createGrade(schoolName, gradeNum);
                }
            }

            gradeRetry++;
        }


        int retry = 0;
        while (retry < 2) {
            // 更新 class_id
            lazyDao.runSql(sql("update import_student_0228 t0 left join org t1 on\n" +
                               "t0.grade_id = t1.parent_id and t0.class_num = t1.class_num\n" +
                               "set t0.class_id = t1.id\n" +
                               "where t1.id is not null "));
            // 检查 class
            List<NutMap> unexistClassList = lazyDao.queryMap(sql("select distinct school_name, class_name, grade_id," +
                                                                 "grade_num, class_num from import_student_0228 where class_id is null"));
            if (Lang.isEmpty(unexistClassList)) {
                break;
            }

            if (retry == 0) {
                for (NutMap nutMap : unexistClassList) {
                    String gradeId = nutMap.getString("gradeId");
                    int gradeNum = nutMap.getInt("gradeNum");
                    int classNum = nutMap.getInt("classNum");

                    Org clazz = new Org();
                    String name = Constants.GradeNum.getGradeClass(gradeNum).getLabel() + classNum + "班";
                    clazz.setOrgName(name);
                    clazz.setGradeNum(gradeNum);
                    clazz.setClassNum(classNum);

                    clazz.setParentId(gradeId);
                    clazz.setOrgType(Constants.OrgType.CLASS.getType());
                    orgService.saveOrg(clazz);
                }
                retry++;
                continue;
            }

            throw new IllegalArgumentException(String.format("班级不存在：\n%s", CollUtil.join(unexistClassList
                            .stream().map(it -> String.format("学校:%s 班级:%s", it.getString("schoolName"), it.getString("className")))
                            .collect(Collectors.toList())
                    , "\n")));
        }

        // 同一班级、同一姓名，更新 username
        lazyDao.runSql(sql("update import_student_0228 t0\n" +
                           "    left join class_has_user t1 on t0.class_id = t1.org_id\n" +
                           "    left join org_user t2 on t1.user_id = t2.id and t2.real_name = t0.student_name\n" +
                           "set t0.user_id = t2.id, t0.username = t2.username\n" +
                           "where t2.id is not null and t0.username is null;"));

        // 根据 username 更新 user_id
        lazyDao.runSql("update import_student_0228 t0 left join org_user t1 on t0.username = t1.username " +
                       "set t0.user_id = t1.id where t1.id is not null;");

        // 检查 username
        List<StudentRecord> unexistUsernameList = lazyDao.selectList(StudentRecord.class, sql("select * from import_student_0228 where user_id is null"));
        log.info("unexistUsernameList size: {}", unexistUsernameList.size());
        unexistUsernameList.parallelStream().forEach(record -> {
            OrgUser user = new OrgUser();
            String username = StrUtil.firstNonBlank(record.getUsername(), genUsername());
            user.setUsername(username);
            user.setRealName(record.getStudentName());
            user.setUserType(0);
            user.setEnabled(true);
            userDao.save(user);

            String userId = user.getId();

            lazyDao.runSql("insert into class_has_user (org_id, user_id, user_type) values (?, ?, 0)", record.getClassId(), userId);
            lazyDao.runSql("insert into school_has_user (org_id, user_id, user_type) values (?, ?, 0)", record.getSchoolId(), userId);
            lazyDao.runSql("insert into org_has_user (org_id, tree_code, user_id, user_type) " +
                           "values (?, ?, ?, 0)", record.campusId, record.getCampusTreeCode(), userId);
            lazyDao.runSql("update import_student_0228 set username = ?, user_id = ? where id = ?", username, userId, record.getId());
        });
        lazyDao.runSql(sql("update class_has_user t0 left join org t1 on t0.org_id = t1.id " +
                           "set t0.tree_code = t1.tree_code where t0.tree_code is null"));
    }

    // 生成用户名，4位字母+4位数字
    String genUsername() {
        return RandomUtil.randomString("abcdefghijklmnopqrstuvwxyz", 4) +
               RandomUtil.randomNumbers(4);
    }

    static class StudentInfo {
        int rowNum;

        String campusName;
        String className;
        String studentName;

        int gradeNum;
        int classNum;

        String username;    // 生成的用户名

        public StudentInfo() {
        }

        public StudentInfo(int rowNum,
                           String campusName,
                           String className, String studentName) {
            this.rowNum = rowNum;
            this.campusName = StrUtil.trimToNull(campusName);
            this.className = StrUtil.trimToNull(className);
            this.studentName = StrUtil.trimToNull(studentName);
        }
    }

    @Data
    @Entity(tableName = "import_student_0228")
    public static class StudentRecord implements Serializable {
        @Id
        @Column(name = "id")
        private Long id;

        @Column(name = "school_name")
        private String schoolName;

        @Column(name = "campus_name")
        private String campusName;

        @Column(name = "class_name")
        private String className;

        @Column(name = "student_name")
        private String studentName;

        @Column(name = "grade_num")
        private Integer gradeNum;

        @Column(name = "class_num")
        private Integer classNum;

        @Column(name = "school_id")
        private Long schoolId;

        @Column(name = "campus_id")
        private Long campusId;

        @Column(name = "campus_tree_code")
        private String campusTreeCode;

        @Column(name = "grade_id")
        private Long gradeId;

        @Column(name = "class_id")
        private Long classId;

        @Column(name = "username")
        private String username;

        @Column(name = "user_id")
        private Long userId;
    }

}
